package com.drore.cloud.sdkjdbc.Jdbc;

import com.drore.cloud.sdkjdbc.model.SqlParameter;
import com.drore.cloud.sdkjdbc.exception.DataSdkException;
import com.drore.cloud.sdkjdbc.utils.GsonUtil;
import com.drore.cloud.sdkjdbc.utils.JdbcUtils;
import com.drore.cloud.sdkjdbc.utils.UUIDUtil;
import com.drore.cloud.sdkjdbc.utils.DateUtil;
import com.google.common.collect.Lists;

import javax.sql.DataSource;
import java.sql.*;
import java.util.*;

/***
 * 浙江卓锐科技股份有限公司 版权所有@Copyright 2016
 * 说明
 * 项目名称
 * @since:cloud-ims 1.0
 * @author <a href="mailto:baoec@drore.com">baoec@drore.com </a> 
 * 2018/04/27 10:03
 */

public abstract class  JDBCTemplate {
    private DataSource dataSource;

    public DataSource getDataSource() {
        return dataSource;
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public JDBCTemplate() {
    }

    public JDBCTemplate(DataSource dataSource) {
        this.dataSource=dataSource;
    }

    /**
     * 根据查询总数*
     * ***/
    public Integer  queryCount(String sql,List<SqlParameter> lists)  {
        Connection connection=null;
        PreparedStatement preparedStatement = null;
        Integer count=0;
        ResultSet rs=null;
        try {
            connection=this.getDataSource().getConnection();
            System.out.println(sql);
            preparedStatement = connection.prepareStatement(sql);
            JDBCStatement.setParams(preparedStatement,lists);
            rs = preparedStatement.executeQuery();
            if (rs.next()){
                count=rs.getInt(1);
            }
            JdbcUtils.closeResultSet(rs);
            JdbcUtils.closeStatement(preparedStatement);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.closeResultSet(rs);
            JdbcUtils.closeStatement(preparedStatement);
            JdbcUtils.closeConnection(connection);
        }
        return count;
    }

    /**
     * 根据查询条件获取 数据集合*
     * ***/
    public List<Map<String, Object>>  queryForList(String sql,List<SqlParameter> lists)   {
        List<Map<String, Object>> result = execute(this.getDataSource(), sql,lists);
        return result;
    }
    /**
     * 根据查询条件获取 数据集合*
     * ***/
    public Map<String, Object>  queryForMap(String sql,List<SqlParameter> lists)   {
        List<Map<String, Object>> result = execute(this.getDataSource(), sql,lists);
        if (result.size()==0){
            throw new DataSdkException("not found data,please check id");
        }
        return result.get(0);
    }

    /**
     * 根据查询总数*
     * ***/
    @Deprecated
    public Integer  queryCountBySql(String sql)  {
        Connection connection=null;
        PreparedStatement preparedStatement = null;
        Integer count=0;
        ResultSet rs=null;
        try {
             connection=this.getDataSource().getConnection();
            preparedStatement = connection.prepareStatement(sql);
             rs = preparedStatement.executeQuery();
            if (rs.next()){
                count=rs.getInt(1);
            }
            JdbcUtils.closeResultSet(rs);
            JdbcUtils.closeStatement(preparedStatement);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.closeResultSet(rs);
            JdbcUtils.closeStatement(preparedStatement);
            JdbcUtils.closeConnection(connection);
        }
        return count;
    }
    /**
     * 根据主键id获取单条数据*
     * ***/
    @Deprecated
    public Map<String, Object>  queryForMapBySql(String sql)  {

        List<Map<String, Object>> list = executeBySql(this.getDataSource(), sql);
        if (list.size()==0){
            throw new DataSdkException("not found data,please check id");
        }
        return list.get(0);
    }
    /**
     * 根据查询条件获取 数据集合*
     * ***/
    @Deprecated
    public List<Map<String, Object>>  queryForListBySql(String sql)   {

        List<Map<String, Object>> result = executeBySql(this.getDataSource(), sql);
        return result;
    }

    /**
     * 具体查询
     * ***/
    @Deprecated
    protected List<Map<String, Object>> executeBySql(DataSource dataSource,String sql){
        Connection con=null;
        Statement stmt = null;
        ResultSet resultSet=null;
        List<Map<String, Object>> result = Lists.newArrayList();
        try {
             con=this.getDataSource().getConnection();
            stmt = con.createStatement();
            resultSet = stmt.executeQuery(sql);
            result = JdbcUtils.readDataLine(resultSet);
        } catch (SQLException var11) {

            JdbcUtils.closeStatement(stmt);
            stmt = null;
            con = null;
            throw new DataSdkException(var11.getLocalizedMessage());
        } finally {
            JdbcUtils.closeStatement(stmt);
            JdbcUtils.closeResultSet(resultSet);
            JdbcUtils.closeConnection(con);
        }
        return result;
    }

    /**
     * 执行数据库批量更新操作  单条update
     *
     * @param datas     更新数据表中key为列名和value为列对应的值的Map对象的List集合
     * @param tableName 要更新的数据库的表名
     * @return 影响的行数
     * @throws SQLException SQL异常
     */
    @Deprecated
    public  String updateBatchBySql(String tableName, List<Map<String, Object>> datas) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        /**要返回的ids字段，其实就是用value拼起来的**/
        StringBuilder ids = new StringBuilder();
        try {
            /**从数据库连接池中获取数据库连接**/
            connection=this.dataSource.getConnection();
            if(datas.size()==0){
                throw new DataSdkException("update Batch list size not 0  ");
            }
            //查具体数据
            //查具体数据
            StringBuffer sql=new StringBuffer("UPDATE  ");
            sql.append(tableName);
            //拼接key
            Map<String, Object> keymap = datas.get(0);
            boolean flag=keymap.containsKey("id");

            if(!flag){
                throw new DataSdkException("update must need 'id' please check . ");
            }
            StringBuffer set=new StringBuffer(" SET ");
            StringBuffer resultid=new StringBuffer("");
            String sp="";
            for (Map.Entry<String, Object> entrykey : keymap.entrySet()) {
                StringBuffer case_s=new StringBuffer("  ");
                case_s.append(entrykey.getKey()).append("=").append(" case id ");
                //拼接when 部分
                StringBuffer when=new StringBuffer("  ");
                for (Map<String,Object> query_data : datas) {
                    StringBuffer then=new StringBuffer(" when ");
                    //拼接then部分
                    String id = Objects.toString(query_data.get("id"));
                    then.append("'").append(id).append("'").append(" then ").append("'").append(entrykey.getValue()).append("'");
                    when.append(then);
                }
                when.append(" end ");
                case_s.append(when);
                set.append(sp).append(case_s);
                sp=",";
            }
            //id 需要单独拼接
            String spid="";
            for (Map<String,Object> query_data : datas) {
                String id = Objects.toString(query_data.get("id"));
                ids.append(spid).append("'").append(id).append("'");
                spid=",";
            }
            sql.append(set).append(" where id in (").append(ids).append(")");
            System.out.println("发送前参数:"+sql.toString());

            /**执行SQL预编译**/
            preparedStatement = connection.prepareStatement(sql.toString());
            /**设置不自动提交，以便于在出现异常的时候数据库回滚**/
            connection.setAutoCommit(false);
            int i = preparedStatement.executeUpdate();
            connection.commit();
            System.out.println("成功了插入了" + i + "行");
            System.out.println("修改的ids为:"+ids);
        } catch (Exception e) {
            if (connection != null) {
                connection.rollback();
            }
            e.printStackTrace();
            //throw e;
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
        return ids.toString();
    }
    /**
     * 具体查询
     * ***/
    protected List<Map<String, Object>> execute(DataSource dataSource,String sql,List<SqlParameter> lists){
        System.out.println("具体查询前sql:"+sql);
        Connection con=null;
        PreparedStatement stmt = null;
        ResultSet resultSet=null;
        List<Map<String, Object>> result = Lists.newArrayList();
        try {
            con=this.getDataSource().getConnection();
            stmt = con.prepareStatement(sql);
            JDBCStatement.setParams(stmt,lists);
            resultSet = stmt.executeQuery();
            result = JdbcUtils.readDataLine(resultSet);
        } catch (SQLException var11) {

            JdbcUtils.closeStatement(stmt);
            stmt = null;
            con = null;
            throw new DataSdkException(var11.getLocalizedMessage());
        } finally {
            JdbcUtils.closeStatement(stmt);
            JdbcUtils.closeResultSet(resultSet);
            JdbcUtils.closeConnection(con);
        }
        return result;
    }

    /**
     * 执行数据库批量插入操作
     *
     * @param datas     插入数据表中key为列名和value为列对应的值的Map对象的List集合
     * @param tableName 要插入的数据库的表名
     * @return 影响的行数
     * @throws SQLException SQL异常
     */
    public  String insertBatch(String tableName, List<Map<String, Object>> datas) throws SQLException {
        /**影响的行数**/
        SQLException is_throw = null;
        int affectRowCount = -1;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        /**要返回的ids字段，其实就是用value拼起来的**/
        StringBuilder ids = new StringBuilder();
        try {
            /**从数据库连接池中获取数据库连接**/
            connection=this.dataSource.getConnection();
            for (Map<String, Object> data : datas) {
                //增加必须的id字段 每张表都需要id
                data.put("id", UUIDUtil.getUUID32());
                //增加必须的 create_time 字段 每张表都需要 create_time
                data.put("create_time", DateUtil.format("yyyy-MM-dd HH:mm:ss"));
            }

            Map<String, Object> valueMap = datas.get(0);
            /**获取数据库插入的Map的键值对的值**/
            Set<String> keySet = valueMap.keySet();
            Iterator<String> iterator = keySet.iterator();
            /**要插入的字段sql，其实就是用key拼起来的**/
            StringBuilder columnSql = new StringBuilder();

            /**要插入的字段值，其实就是？**/
            StringBuilder unknownMarkSql = new StringBuilder();
            Object[] keys = new Object[valueMap.size()];
            int i = 0;
            while (iterator.hasNext()) {
                String key = iterator.next();
                keys[i] = key;
                columnSql.append(i == 0 ? "" : ",");
                columnSql.append(key);

                unknownMarkSql.append(i == 0 ? "" : ",");
                unknownMarkSql.append("?");
                i++;
            }
            /**开始拼插入的sql语句**/
            StringBuilder sql = new StringBuilder();
            sql.append("INSERT INTO ");
            sql.append(tableName);
            sql.append(" (");
            sql.append(columnSql);
            sql.append(" )  VALUES (");
            sql.append(unknownMarkSql);
            sql.append(" )");

            /**执行SQL预编译**/
            preparedStatement = connection.prepareStatement(sql.toString());
            /**设置不自动提交，以便于在出现异常的时候数据库回滚**/
            connection.setAutoCommit(false);
            System.out.println(sql.toString());
            for (int j = 0; j < datas.size(); j++) {
                for (int k = 0; k < keys.length; k++) {
                    preparedStatement.setObject(k + 1, datas.get(j).get(keys[k]));
                    if(keys[k].equals("id")){
                        ids.append(j == 0 ? "" : ",");
                        ids.append(datas.get(j).get(keys[k]));
                    }
                }
                preparedStatement.addBatch();
            }
            int[] arr = preparedStatement.executeBatch();
            connection.commit();
            affectRowCount = arr.length;
            System.out.println("成功了插入了" + affectRowCount + "行");
            System.out.println("插入的ids为:"+ids);
        } catch (SQLException e) {
            is_throw=e;
            if (connection != null) {
                connection.rollback();
            }
        }
        finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (connection != null) {
                connection.close();
            }
            if(is_throw!=null){
                throw is_throw;
            }
        }
        return ids.toString();
    }

    /**
     * 执行数据库批量更新操作 多条update
     *
     * @param datas     更新数据表中key为列名和value为列对应的值的Map对象的List集合
     * @param tableName 要更新的数据库的表名
     * @return 影响的行数
     * @throws SQLException SQL异常
     */
    public  String updateBatch(String tableName, List<Map<String, Object>> datas) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        /**要返回的ids字段，其实就是用value拼起来的**/
        StringBuilder ids = new StringBuilder();
        try {
            /**从数据库连接池中获取数据库连接**/
            connection=this.dataSource.getConnection();
            if(datas.size()==0){
                throw new DataSdkException("update Batch list size not 0  ");
            }
            //拼接key
            Map<String, Object> keymap = datas.get(0);
            boolean flag=keymap.containsKey("id");
            if(!flag){
                throw new DataSdkException("update must need 'id' please check . ");
            }
            /**获取数据库插入的Map的键值对的值**/
            Set<String> keySet = keymap.keySet();
            Iterator<String> iterator = keySet.iterator();
            /**要插入的字段sql，其实就是用key和?拼接起来的**/
            StringBuilder columnUpdate = new StringBuilder();

            Object[] keys = new Object[keymap.size()];
            int i = 0;
            while (iterator.hasNext()) {
                String key = iterator.next();
                keys[i] = key;
                columnUpdate.append(i == 0 ? "" : ",");
                columnUpdate.append(key);
                columnUpdate.append("=");
                columnUpdate.append("?");
                i++;

            }
            /**开始拼更新的sql语句**/
            StringBuilder updatesql = new StringBuilder();
            updatesql.append("UPDATE ");
            updatesql.append(tableName);
            updatesql.append(" SET ");
            updatesql.append(columnUpdate);
            updatesql.append(" where id = ? ");

            System.out.println("打印下更新模板sql:"+updatesql);
            /**执行SQL预编译**/
            preparedStatement = connection.prepareStatement(updatesql.toString());
            /**设置不自动提交，以便于在出现异常的时候数据库回滚**/
            connection.setAutoCommit(false);
            for (int j=0;j<datas.size();j++) {
                for (int k=0;k<keys.length;k++) {
                    preparedStatement.setObject(k+1,datas.get(j).get(keys[k]));
                }
                preparedStatement.setObject(keys.length+1,datas.get(j).get("id"));
                ids.append(j==0?"":",").append(datas.get(j).get("id"));
                preparedStatement.addBatch();
            }
            int[] arr = preparedStatement.executeBatch();
            connection.commit();
            System.out.println("成功了修改了" + arr.length + "行");
            System.out.println("修改的ids为:"+ids);
        } catch (Exception e) {
            if (connection != null) {
                connection.rollback();
            }
            e.printStackTrace();
            //throw e;
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
        return ids.toString();
    }

    /**
     * 执行数据库批量删除操作
     *
     * @param pkids     删除数据表中key为列名和value为列对应的值的Map对象的List集合
     * @param tableName 要删除的数据库的表名    目前主键 都是string类型
     * @return 影响的行数
     * @throws SQLException SQL异常
     */
    public  String deleteBatch(String tableName, String... pkids) throws SQLException {
        Connection connection = null;
        PreparedStatement statement = null;
        /**要返回的ids字段，其实就是用value拼起来的**/
        StringBuffer ids = new StringBuffer();
        try {
            /**从数据库连接池中获取数据库连接**/
            //connection = DataSourceUtils.getConnection(this.getDataSource());
            connection=this.getDataSource().getConnection();
            if (pkids==null||pkids.length==0) {
                throw new DataSdkException("delete pkids can not be empty");
            }
            for (int i=0;i<pkids.length;i++) {
                ids.append(i==0?"":",");
                ids.append("?");
            }
            String deleteSql = "delete from "+tableName+" where id in ("+ids.toString()+")";
            System.out.println("****sql输出*****"+deleteSql);
            /**执行SQL预编译**/
            statement = connection.prepareStatement(deleteSql);
            for (int i=0;i<pkids.length;i++) {
                statement.setObject(i+1,pkids[i]);
            }
            /**设置不自动提交，以便于在出现异常的时候数据库回滚**/
            connection.setAutoCommit(false);
            int update = statement.executeUpdate();
            connection.commit();
            System.out.println("成功了删除了" + update + "行");
            System.out.println("删除的ids为:"+ids);
        } catch (Exception e) {
            e.printStackTrace();
            if (connection != null) {
                connection.rollback();
            }
            //throw e;
        } finally {
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
        return ids.toString();
    }



    /**
     * 执行数据库  操作
     *
     * @return 操作成功状态
     * @throws SQLException SQL异常
     */
    public  boolean executSql(String sql) throws SQLException {
        boolean is_issue=false;
        SQLException is_throw = null;
        Connection connection = null;
        Statement statement = null;
        /**要返回的ids字段，其实就是用value拼起来的**/
        StringBuffer ids = new StringBuffer();
        try {
            /**从数据库连接池中获取数据库连接**/
            //connection = DataSourceUtils.getConnection(this.getDataSource());
            connection=this.getDataSource().getConnection();

            System.out.println("****sql输出*****"+sql);
            /**执行SQL预编译**/
            statement = connection.createStatement();

            /**设置不自动提交，以便于在出现异常的时候数据库回滚**/
            connection.setAutoCommit(false);
            int update = statement.executeUpdate(sql);
            if (update == 0) {
                System.out.println("表已经创建成功！");
                is_issue=true;
            }
            connection.commit();
        } catch (SQLException e) {
            is_throw=e;
            if (connection != null) {
                connection.rollback();
            }
        } finally {
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
            if(is_throw!=null){
                throw is_throw;
            }
        }
        return is_issue;
    }

    /**
     * 批量 执行数据库   操作
     *
     * @return 操作成功状态
     * @throws SQLException SQL异常
     */
    public  boolean executBatchSql(String... sql) throws SQLException {
        boolean is_issue=false;
        SQLException is_throw = null;
        Connection connection = null;
        Statement statement = null;
        /**要返回的ids字段，其实就是用value拼起来的**/
        StringBuffer ids = new StringBuffer();
        try {
            /**从数据库连接池中获取数据库连接**/
            //connection = DataSourceUtils.getConnection(this.getDataSource());
            connection=this.getDataSource().getConnection();

            System.out.println("****sql输出*****"+ GsonUtil.create().toJson(sql));
            /**执行SQL预编译**/
            statement = connection.createStatement();

            for (String s : sql) {
                statement.addBatch(s);
            }
            int[] ints = statement.executeBatch();
            /**设置不自动提交，以便于在出现异常的时候数据库回滚**/
            connection.setAutoCommit(false);
            //int update = statement.executeUpdate(sql);
            if (ints.length == 0) {
                System.out.println("批量执行失败！");
                is_issue=false;
            }else {
                System.out.println("批量执行成功！");
                is_issue=true;
            }
            connection.commit();
        } catch (SQLException e) {
            is_throw=e;
            if (connection != null) {
                connection.rollback();
            }
        } finally {
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
            if(is_throw!=null){
                throw is_throw;
            }
        }
        return is_issue;
    }


    /**
     * 测试是否连接上了数据库   操作
     *
     * @return 操作成功状态
     * @throws SQLException SQL异常
     */
    public  boolean connectionTest() throws SQLException {
        boolean is_issue=false;
        SQLException is_throw = null;
        Connection connection = null;

        try {
            connection=this.getDataSource().getConnection();
            if(connection!=null){
                is_issue=true;
            }

        } catch (SQLException e) {
            is_throw=e;
            if (connection != null) {
                connection.rollback();
            }
        } finally {
            if (connection != null) {
                connection.close();
            }
            if(is_throw!=null){
                throw is_throw;
            }
        }
        return is_issue;
    }
}
